Working with Numbers

Visual Basic offers a rich assortment of math operators and functions. Most of these operators are polymorphic in the sense that they work equally well with arguments of any type, including Integer, Long, Single, Double, Date, and Currency. Depending on the particular operator or function, the Visual Basic compiler can decide to convert the operands to a more suitable data type. However, this is the language's job, and you don't have to worry because everything is done automatically for you.

As you know, Visual Basic supports all four math operators. When combining two values of different types, Visual Basic automatically applies the data coercion and converts the simpler type to the more comprehensive one (for example, Integer to Long or Single to Double). Interestingly, the division operator (/) always converts both its operands to Double, which can cause some unexpected overhead. If you're dividing an Integer or Long number by another Integer or Long number and you aren't interested in the decimal part of the quotient, you should use the integer division operator (\), which executes faster:

Dim a As Long, b As Long, result As Long result = a / b ' Floating point division result = a \ b ' This is about 4 times faster.

Visual Basic also supports the exponentiation operator (^), which raises a number to an exponent. In this case, the result is always of type Double, even if you're raising an integer number to an integer exponent. In general, the ^ operator is relatively slow, and for small integer exponents you might decide to use a chain of multiplication operations instead:

Dim x As Double, result As Double x = 1.2345 result = x ^ 3 result = x * x * x ' This is about 20 times faster.

The MOD operator extracts the remainder of a division between integer values. It's often used to test whether a number is an exact multiple of another number. This operator is very efficient but has a limitation: It converts its operands to Long and therefore can't be used with arbitrarily large values. It also truncates any decimal part. Here's a function that works with any Double value:

Function FPMod(ByVal Number As Double, ByVal divisor As Double) As Double ' Note: this differs from MOD when Number is negative. FPMod = Number - Int(Number / divisor) * divisor End Function

Several other functions are often useful when you're working with numbers:

• Abs returns the absolute value of its argument.
• Sgn returns -1, 0, or +1 if the argument is negative, zero, or positive, respectively.
• Sqr returns the square root of a number.
• Exp raises e (the base of natural logarithms) to the power passed in the argument.
• Log returns the natural logarithm of its argument. You can evaluate a decimal logarithm using the following function:

Function Log10(Number As Double) As Double Log10 = Log(Number) / 2.30258509299405 End Function

Visual Basic supports six comparison operators, which can be applied to both numeric and string operands:

= < <= > >= <>

These operators are often used in If blocks, but you should keep in mind that they aren't conceptually different from any other math operators, in the sense that they accept two operands and deliver a result. Such a result can be False (0) or True (-1). You can sometimes exploit this fact to write more concise code, as in the following:

' The following lines are equivalent. If x > y Then x = x _ 1 x = x + (x > y)

Visual Basic for Applications supports a few Boolean operators, which are especially useful for combining multiple Boolean subexpressions. The operators used most frequently are AND, OR, XOR, and NOT. For example, the following code uses Boolean operators to determine the signs of two variables:

If (x > 0) And (y > 0) Then ' Both X and Y are positive. ElseIf (x = 0) Or (y = 0) Then ' Either X or Y (or both) are zero. ElseIf (x > 0) Xor (y > 0) Then ' Either X or Y (but not both of them) are positive. ElseIf Not (x > 0) Then ' X is not positive. End If

Remember that these operators are actually bit-wise operators, in that they act on each individual bit of their operands. In practice, this can make a difference if the operands aren't Boolean values (that is, they have a value different from -1 and 0). You can use the AND operator to test one or more bits of a number:

If (number And 1) Then Print "Bit 0 is set (number is an odd value)" If (number And 6) = 6 Then Print "Both bits 1 and 2 are set" If (number And 6) Then Print "Either bits 1 and 2, or both, are set"

You usually use the OR operator to set one or more bits:

number = number Or 4 ' Set bit 2. number = number Or (8 + 1) ' Set bits 3 and 0.

To reset one or more bits, you combine the AND and NOT operators:

Number = number And Not 4 ' Reset bit 2.

Finally you use the XOR operator to flip the state of one or more bits:

Number = number Xor 2 ' Flip the state of bit 1.

If you don't know at compile time which bit should be set, reset, or flipped, you can use the exponentiation operator, as in the following code:

Number = Number Or (2 ^ N) ' Set Nth bit (N in range 0-30).

This approach has two defects: It raises an overflow error if N = 31, and it's highly inefficient because it relies on a floating-point operation. You can solve both problems with the following function:

Function Power2(ByVal exponent As Long) As Long Static result(0 To 31) As Long, i As Integer ' Evaluate all powers of 2 only once. If result(0) = 0 Then result(0) = 1 For i = 1 To 30 result(i) = result(i - 1) * 2 Next result(31) = &H80000000 ' This is a special value. End If Power2 = result(exponent) End Function

The Int function truncates a number to the integer value equal or lower than its argument. This is different from just saying "truncates the decimal part of a number." The difference becomes apparent if the argument is negative:

Print Int(1.2) ' Displays "1" Print Int(-1.2) ' Displays "-2"

The function that actually truncates the decimal part of a number is Fix:

Print Fix(1.2) ' Displays "1" Print Fix(-1.2) ' Displays "-1"

Visual Basic 6 introduces a new math function, Round, which lets you round a decimal number to the number of digits you want (or to the nearest integer, if the second argument is omitted):

Print Round(1.45) ' Displays "1" Print Round(1.55) ' Displays "2" Print Round(1.23456, 4) ' Displays "1.2346"

Round has an undocumented quirk: When the fractional part is exactly 0.5, it rounds up if the integer portion is an odd number and rounds down if it's even:

Print Round(1.5), Round(2.5) ' Both display "2".

This behavior is necessary so that you can avoid introducing errors when you're doing statistical evaluations, and it shouldn't be considered a bug.

When rounding, you sometimes need to determine the nearest integer higher or equal to the argument, but Visual Basic lacks such a function. You can remedy this problem with this short routine:

Function Ceiling(number As Double) As Long Ceiling = -Int(-number) End Function

VBA supports numeric constants in decimal, hexadecimal, and octal systems:

value = &H1234 ' The value 4660 as a hexadecimal constant value = &O11064 ' The same value as octal constant

You can convert any hexadecimal or octal string into its decimal value using the Val function:

' If Text1 holds a hexadecimal value value = Val("&H" & Text1.Text)

You do the opposite conversion—from decimal to hexadecimal or octal—using the Hex and Oct functions:

Text1.Text = Hex$(value)

Oddly, Visual Basic doesn't include a function that converts to and from binary numbers, which are by far more common than octal values. You can achieve these conversions using a pair of functions, which build on the Power2 function seen in the section "Boolean and Bit-Wise Operators" earlier in this chapter:

' Convert from decimal to binary. Function Bin(ByVal value As Long) As String Dim result As String, exponent As Integer ' This is faster than creating the string by appending chars. result = String$(32, "0") Do If value And Power2(exponent) Then ' We found a bit that is set, clear it. Mid$(result, 32 - exponent, 1) = "1" value = value Xor Power2(exponent) End If exponent = exponent + 1 Loop While value Bin = Mid$(result, 33 - exponent) ' Drop leading zeros. End Function ' Convert from binary to decimal. Function BinToDec(value As String) As Long Dim result As Long, i As Integer, exponent As Integer For i = Len(value) To 1 Step -1 Select Case Asc(Mid$(value, i, 1)) Case 48 ' "0", do nothing. Case 49 ' "1", add the corresponding power of 2. result = result + Power2(exponent) Case Else Err.Raise 5 ' Invalid procedure call or argument End Select exponent = exponent + 1 Next BinToDec = result End Function

All versions of the VBA language include the Format function, which is a powerful tool that meets most of your formatting requirements. Its syntax is rather complex:

result = Format(Expression, [Format], _ [FirstDayOfWeek As VbDayOfWeek = vbSunday], _ [FirstWeekOfYear As VbFirstWeekOfYear = vbFirstJan1])

Fortunately, the first two arguments are sufficient for all your tasks unless you're formatting dates, which I'll talk about later in this chapter. Right now I'll summarize the Format function's many capabilities when formatting numeric values, although I suggest that you have a look at Visual Basic documentation for more details.

When formatting numbers, the Format function supports both named formats and custom formats. Named formats include the following strings: General Number (no special formatting, use the scientific notation if needed), Currency (currency symbol, thousand separator and two decimal digits), Fixed (two decimal digits), Standard (thousand separator and two decimal digits), Percent (a percentage, with the % symbol appended), Scientific (scientific notation), Yes/No, True/False, On/Off (False or Off if 0, True or On otherwise). Format is a locale-aware function and automatically uses the currency symbol, the thousand separator, and the decimal separator that are appropriate to the current locale.

If a named format doesn't do the job, you can create your own custom format using a format string made up of special characters. (For a detailed list and the meaning of such formatting characters, see the Visual Basic documentation.)

' Decimal and thousand separators. (Format rounds its result.) Print Format(1234.567, "#,##0.00") ' "1,234.57" ' Percentage values Print Format(0.234, "#.#%") ' "23.4%" ' Scientific notation Print Format(12345.67, "#.###E+") ' "1.235E+4" Print Format(12345.67, "#.###E-") ' "1.235E4"

A great feature of the Format function is its ability to apply different format strings if the number is positive, negative, 0, or Null. You use the semicolon as the delimiter of the section in the custom format string. (You can specify one, two, three, or four different sections.)

' Two decimal digits for positive numbers, enclose negative numbers within ' a pair of parentheses, use a blank for zero, and "N/A" for Null values. Print Format(number, "##,###.00;(##,###.00); ;N/A")

Visual Basic 6 has introduced three new formatting functions for numbers—namely FormatNumber, FormatPercent, and FormatCurrency—that have been borrowed from VBScript. (Three more functions—FormatDate, MonthName, and WeekdayName—are explained in the section "Working with Dates," later in this chapter.) These new functions duplicate the capabilities of the more powerful, all-in-one Format workhorse, but their syntax is more intuitive, as you can see in the code below.

result = FormatNumber(expr, [DecDigits], [InclLeadingDigit], _ [UseParens], [GroupDigits] ) result = FormatPercent(expr, [DecDigits], [InclLeadingDigit], _ [UseParens], [GroupDigits] ) result = FormatCurrency(expr, [DecDigits], [InclLeadingDigit], _ [UseParens], [,GroupDigits] )

In all cases, DecDigits is the number of decimal digits you want (2 is the default); InclLeadingDigit tells whether numbers in the range [-1,1] are displayed with a leading 0; UseParens specifies whether negative numbers are enclosed in parentheses; GroupDigits tells whether a thousand separator should be used. The last three optional arguments can each be one of the following values: 0-vbFalse, -1-vbTrue, or -2-vbUseDefault (the default setting for the user's locale). If you omit a value, vbUseDefault is assumed by default.

At times, you need to generate one or more random values. Among the types of software for which you need to do this, games come to mind, but this ability is also useful in business applications that include simulations. Visual Basic offers only one statement and one function for generating random values. You initialize the seed of the internal random number generators using the Randomize statement. You can pass it a number that will be used as a seed; otherwise, Visual Basic automatically uses the value returned by the Timer function:

Randomize 10

The Rnd function returns a random value each time you call it. The returned value is always less than 1 and greater than or equal to 0, so you need to scale the result to get a number in the range you want:

' Simple computerized dice Randomize For i = 1 To 10 Print Int(Rnd * 6) + 1 Next

At times, you might want to repeat the same sequence of random numbers, especially when debugging your code. It might seem that you can obtain this behavior by calling the Randomize statement with the same seed, but this isn't so. Instead, as counterintuitive as it may seem, to repeat the same random sequence you call the Rnd function with a negative argument:

dummy = Rnd(-1) ' Initialize the seed. (No Randomize is needed!) For i = 1 To 10 ' This loop will always deliver the same Print Int(Rnd * 6) + 1 ' sequence of random numbers. Next

You can also reread the random number that you have just generated by passing 0 as an argument to Rnd.

A common task when you're dealing with random numbers is the generation of a casual permutation of the numbers in a given range: for example, this might be useful for shuffling a deck of cards in a game. Here's a simple and efficient routine that returns an array of all Long numbers in the range of first and last, in random order:

Function RandomArray(first As Long, last As Long) As Long() Dim i As Long, j As Long, temp As Long ReDim result(first To last) As Long ' Initialize the array. For i = first To last: result(i) = i: Next ' Now shuffle it. For i = last To first Step -1 ' Generate a random number in the proper range. j = Rnd * (last - first + 1) + first ' Swap the two items. temp = result(i): result(i) = result(j): result(j) = temp Next RandomArray = result End Function